{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['中欧医疗健康混合A (003095).csv',\n",
       " '中欧时代先锋股票A (001938).csv',\n",
       " '兴全合润混合(LOF) (163406).csv',\n",
       " '兴全趋势投资混合(LOF) (163402).csv',\n",
       " '博时科创板三年定开混合 (506005).csv',\n",
       " '博时荣享回报混合A (006158).csv',\n",
       " '富国天惠成长混合AB(LOF) (161005).csv',\n",
       " '易方达蓝筹精选混合 (005827).csv',\n",
       " '添富价值创造定开混合 (005379).csv']"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# 筛选出包含扩展名的文件\n",
    "included_extensions = ['csv']\n",
    "# 遍历的文件\n",
    "path = '.'\n",
    "file_names = [fn for fn in os.listdir(path)\n",
    "              if any(fn.endswith(ext) for ext in included_extensions)]\n",
    "file_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 持仓占比\n",
    "total = 116151.49\n",
    "my_fund = {'博时科创板三年定开混合 (506005)':27452.43/total,\n",
    "           '富国天惠成长混合AB(LOF) (161005)':18341.33/total,\n",
    "           '兴全合润混合(LOF) (163406)':18253.30/total,\n",
    "           '中欧医疗健康混合A (003095)':16639.69/total,\n",
    "           '添富价值创造定开混合 (005379)':15274.85/total,\n",
    "           '易方达蓝筹精选混合 (005827)':9655.21/total,\n",
    "           '兴全趋势投资混合(LOF) (163402)':6028/total,\n",
    "           '博时荣享回报混合A (006158)':3062.38/total,\n",
    "           '中欧时代先锋股票A (001938)':1444.30/total,\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>688036</td>\n",
       "      <td>传音控股</td>\n",
       "      <td>0.024108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>688536</td>\n",
       "      <td>思瑞浦</td>\n",
       "      <td>0.020917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>688185</td>\n",
       "      <td>康希诺</td>\n",
       "      <td>0.012456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>688111</td>\n",
       "      <td>金山办公</td>\n",
       "      <td>0.009738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00700</td>\n",
       "      <td>腾讯控股</td>\n",
       "      <td>0.009099</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code  name  percentage\n",
       "0  688036  传音控股    0.024108\n",
       "1  688536   思瑞浦    0.020917\n",
       "2  688185   康希诺    0.012456\n",
       "3  688111  金山办公    0.009738\n",
       "4   00700  腾讯控股    0.009099"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(columns=['code','name','percentage'])\n",
    "for key,value in my_fund.items():\n",
    "    fund_df = pd.read_csv(key+\".csv\",dtype={'code': str})\n",
    "    fund_df['percentage'] = fund_df['percentage'].map(lambda x:float(x.strip().rstrip('%'))*0.01)\n",
    "    fund_df['percentage'] = fund_df['percentage'] * value\n",
    "    df = df.append(fund_df, ignore_index=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>00700</td>\n",
       "      <td>腾讯控股</td>\n",
       "      <td>0.029074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>600519</td>\n",
       "      <td>贵州茅台</td>\n",
       "      <td>0.026675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>688036</td>\n",
       "      <td>传音控股</td>\n",
       "      <td>0.024108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>000858</td>\n",
       "      <td>五粮液</td>\n",
       "      <td>0.023657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>688536</td>\n",
       "      <td>思瑞浦</td>\n",
       "      <td>0.020917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>300122</td>\n",
       "      <td>智飞生物</td>\n",
       "      <td>0.016150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>002415</td>\n",
       "      <td>海康威视</td>\n",
       "      <td>0.015070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>601318</td>\n",
       "      <td>中国平安</td>\n",
       "      <td>0.014735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>603259</td>\n",
       "      <td>药明康德</td>\n",
       "      <td>0.013237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>688185</td>\n",
       "      <td>康希诺</td>\n",
       "      <td>0.012456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>300015</td>\n",
       "      <td>爱尔眼科</td>\n",
       "      <td>0.012220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>03690</td>\n",
       "      <td>美团-W</td>\n",
       "      <td>0.012006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>0.011533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000568</td>\n",
       "      <td>泸州老窖</td>\n",
       "      <td>0.011424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>600690</td>\n",
       "      <td>海尔智家</td>\n",
       "      <td>0.011378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>601888</td>\n",
       "      <td>中国中免</td>\n",
       "      <td>0.011088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>688111</td>\n",
       "      <td>金山办公</td>\n",
       "      <td>0.009738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>600036</td>\n",
       "      <td>招商银行</td>\n",
       "      <td>0.009490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000661</td>\n",
       "      <td>长春高新</td>\n",
       "      <td>0.009398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>601166</td>\n",
       "      <td>兴业银行</td>\n",
       "      <td>0.009162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>002821</td>\n",
       "      <td>凯莱英</td>\n",
       "      <td>0.009083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>688396</td>\n",
       "      <td>华润微</td>\n",
       "      <td>0.008674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>300347</td>\n",
       "      <td>泰格医药</td>\n",
       "      <td>0.008553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>00388</td>\n",
       "      <td>香港交易所</td>\n",
       "      <td>0.008346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>600763</td>\n",
       "      <td>通策医疗</td>\n",
       "      <td>0.008080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>300760</td>\n",
       "      <td>迈瑞医疗</td>\n",
       "      <td>0.007937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>300759</td>\n",
       "      <td>康龙化成</td>\n",
       "      <td>0.007621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>600809</td>\n",
       "      <td>山西汾酒</td>\n",
       "      <td>0.007522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>06098</td>\n",
       "      <td>碧桂园服务</td>\n",
       "      <td>0.007522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>688169</td>\n",
       "      <td>石头科技</td>\n",
       "      <td>0.007445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>600276</td>\n",
       "      <td>恒瑞医药</td>\n",
       "      <td>0.007321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>688050</td>\n",
       "      <td>爱博医疗</td>\n",
       "      <td>0.007232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>688002</td>\n",
       "      <td>睿创微纳</td>\n",
       "      <td>0.007161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>600031</td>\n",
       "      <td>三一重工</td>\n",
       "      <td>0.007135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>688016</td>\n",
       "      <td>心脉医疗</td>\n",
       "      <td>0.006500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>600887</td>\n",
       "      <td>伊利股份</td>\n",
       "      <td>0.005479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>600754</td>\n",
       "      <td>锦江酒店</td>\n",
       "      <td>0.005437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>002142</td>\n",
       "      <td>宁波银行</td>\n",
       "      <td>0.005416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>603501</td>\n",
       "      <td>韦尔股份</td>\n",
       "      <td>0.005195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>000739</td>\n",
       "      <td>普洛药业</td>\n",
       "      <td>0.004950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>603707</td>\n",
       "      <td>健友股份</td>\n",
       "      <td>0.004825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>300413</td>\n",
       "      <td>芒果超媒</td>\n",
       "      <td>0.004793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>300285</td>\n",
       "      <td>国瓷材料</td>\n",
       "      <td>0.004406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>601899</td>\n",
       "      <td>紫金矿业</td>\n",
       "      <td>0.004204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000651</td>\n",
       "      <td>格力电器</td>\n",
       "      <td>0.003916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>002304</td>\n",
       "      <td>洋河股份</td>\n",
       "      <td>0.003890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>01999</td>\n",
       "      <td>敏华控股</td>\n",
       "      <td>0.003538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>600845</td>\n",
       "      <td>宝信软件</td>\n",
       "      <td>0.003442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>600703</td>\n",
       "      <td>三安光电</td>\n",
       "      <td>0.002974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002</td>\n",
       "      <td>万科A</td>\n",
       "      <td>0.002636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>002044</td>\n",
       "      <td>美年健康</td>\n",
       "      <td>0.002434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>600048</td>\n",
       "      <td>保利地产</td>\n",
       "      <td>0.002335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>600309</td>\n",
       "      <td>万华化学</td>\n",
       "      <td>0.002143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>000786</td>\n",
       "      <td>北新建材</td>\n",
       "      <td>0.001853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>603833</td>\n",
       "      <td>欧派家居</td>\n",
       "      <td>0.001707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>300144</td>\n",
       "      <td>宋城演艺</td>\n",
       "      <td>0.001578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>600426</td>\n",
       "      <td>华鲁恒升</td>\n",
       "      <td>0.001318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>002460</td>\n",
       "      <td>赣锋锂业</td>\n",
       "      <td>0.000921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>300014</td>\n",
       "      <td>亿纬锂能</td>\n",
       "      <td>0.000852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>300454</td>\n",
       "      <td>深信服</td>\n",
       "      <td>0.000798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>000708</td>\n",
       "      <td>中信特钢</td>\n",
       "      <td>0.000722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>300750</td>\n",
       "      <td>宁德时代</td>\n",
       "      <td>0.000699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>002352</td>\n",
       "      <td>顺丰控股</td>\n",
       "      <td>0.000678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>002311</td>\n",
       "      <td>海大集团</td>\n",
       "      <td>0.000618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>601658</td>\n",
       "      <td>邮储银行</td>\n",
       "      <td>0.000567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>600406</td>\n",
       "      <td>国电南瑞</td>\n",
       "      <td>0.000555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>600660</td>\n",
       "      <td>福耀玻璃</td>\n",
       "      <td>0.000537</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      code   name  percentage\n",
       "18   00700   腾讯控股    0.029074\n",
       "40  600519   贵州茅台    0.026675\n",
       "60  688036   传音控股    0.024108\n",
       "8   000858    五粮液    0.023657\n",
       "66  688536    思瑞浦    0.020917\n",
       "24  300122   智飞生物    0.016150\n",
       "14  002415   海康威视    0.015070\n",
       "50  601318   中国平安    0.014735\n",
       "54  603259   药明康德    0.013237\n",
       "64  688185    康希诺    0.012456\n",
       "23  300015   爱尔眼科    0.012220\n",
       "20   03690   美团-W    0.012006\n",
       "0   000001   平安银行    0.011533\n",
       "2   000568   泸州老窖    0.011424\n",
       "42  600690   海尔智家    0.011378\n",
       "52  601888   中国中免    0.011088\n",
       "62  688111   金山办公    0.009738\n",
       "34  600036   招商银行    0.009490\n",
       "4   000661   长春高新    0.009398\n",
       "49  601166   兴业银行    0.009162\n",
       "16  002821    凯莱英    0.009083\n",
       "65  688396    华润微    0.008674\n",
       "27  300347   泰格医药    0.008553\n",
       "17   00388  香港交易所    0.008346\n",
       "45  600763   通策医疗    0.008080\n",
       "32  300760   迈瑞医疗    0.007937\n",
       "31  300759   康龙化成    0.007621\n",
       "46  600809   山西汾酒    0.007522\n",
       "21   06098  碧桂园服务    0.007522\n",
       "63  688169   石头科技    0.007445\n",
       "36  600276   恒瑞医药    0.007321\n",
       "61  688050   爱博医疗    0.007232\n",
       "58  688002   睿创微纳    0.007161\n",
       "33  600031   三一重工    0.007135\n",
       "59  688016   心脉医疗    0.006500\n",
       "48  600887   伊利股份    0.005479\n",
       "44  600754   锦江酒店    0.005437\n",
       "10  002142   宁波银行    0.005416\n",
       "55  603501   韦尔股份    0.005195\n",
       "6   000739   普洛药业    0.004950\n",
       "56  603707   健友股份    0.004825\n",
       "28  300413   芒果超媒    0.004793\n",
       "26  300285   国瓷材料    0.004406\n",
       "53  601899   紫金矿业    0.004204\n",
       "3   000651   格力电器    0.003916\n",
       "11  002304   洋河股份    0.003890\n",
       "19   01999   敏华控股    0.003538\n",
       "47  600845   宝信软件    0.003442\n",
       "43  600703   三安光电    0.002974\n",
       "1   000002    万科A    0.002636\n",
       "9   002044   美年健康    0.002434\n",
       "35  600048   保利地产    0.002335\n",
       "37  600309   万华化学    0.002143\n",
       "7   000786   北新建材    0.001853\n",
       "57  603833   欧派家居    0.001707\n",
       "25  300144   宋城演艺    0.001578\n",
       "39  600426   华鲁恒升    0.001318\n",
       "15  002460   赣锋锂业    0.000921\n",
       "22  300014   亿纬锂能    0.000852\n",
       "29  300454    深信服    0.000798\n",
       "5   000708   中信特钢    0.000722\n",
       "30  300750   宁德时代    0.000699\n",
       "13  002352   顺丰控股    0.000678\n",
       "12  002311   海大集团    0.000618\n",
       "51  601658   邮储银行    0.000567\n",
       "38  600406   国电南瑞    0.000555\n",
       "41  600660   福耀玻璃    0.000537"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('max_row',100)\n",
    "data = df.groupby(['code','name'],as_index=False).sum().sort_values('percentage',ascending=False)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>HK0700</td>\n",
       "      <td>腾讯控股</td>\n",
       "      <td>0.029074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>600519</td>\n",
       "      <td>贵州茅台</td>\n",
       "      <td>0.026675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>688036</td>\n",
       "      <td>传音控股</td>\n",
       "      <td>0.024108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>000858</td>\n",
       "      <td>五粮液</td>\n",
       "      <td>0.023657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>688536</td>\n",
       "      <td>思瑞浦</td>\n",
       "      <td>0.020917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>300122</td>\n",
       "      <td>智飞生物</td>\n",
       "      <td>0.016150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>002415</td>\n",
       "      <td>海康威视</td>\n",
       "      <td>0.015070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>601318</td>\n",
       "      <td>中国平安</td>\n",
       "      <td>0.014735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>603259</td>\n",
       "      <td>药明康德</td>\n",
       "      <td>0.013237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>688185</td>\n",
       "      <td>康希诺</td>\n",
       "      <td>0.012456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>300015</td>\n",
       "      <td>爱尔眼科</td>\n",
       "      <td>0.012220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>HK3690</td>\n",
       "      <td>美团-W</td>\n",
       "      <td>0.012006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>0.011533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000568</td>\n",
       "      <td>泸州老窖</td>\n",
       "      <td>0.011424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>600690</td>\n",
       "      <td>海尔智家</td>\n",
       "      <td>0.011378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>601888</td>\n",
       "      <td>中国中免</td>\n",
       "      <td>0.011088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>688111</td>\n",
       "      <td>金山办公</td>\n",
       "      <td>0.009738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>600036</td>\n",
       "      <td>招商银行</td>\n",
       "      <td>0.009490</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000661</td>\n",
       "      <td>长春高新</td>\n",
       "      <td>0.009398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>601166</td>\n",
       "      <td>兴业银行</td>\n",
       "      <td>0.009162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>002821</td>\n",
       "      <td>凯莱英</td>\n",
       "      <td>0.009083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>688396</td>\n",
       "      <td>华润微</td>\n",
       "      <td>0.008674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>300347</td>\n",
       "      <td>泰格医药</td>\n",
       "      <td>0.008553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>HK0388</td>\n",
       "      <td>香港交易所</td>\n",
       "      <td>0.008346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>600763</td>\n",
       "      <td>通策医疗</td>\n",
       "      <td>0.008080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>300760</td>\n",
       "      <td>迈瑞医疗</td>\n",
       "      <td>0.007937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>300759</td>\n",
       "      <td>康龙化成</td>\n",
       "      <td>0.007621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>600809</td>\n",
       "      <td>山西汾酒</td>\n",
       "      <td>0.007522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>HK6098</td>\n",
       "      <td>碧桂园服务</td>\n",
       "      <td>0.007522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>688169</td>\n",
       "      <td>石头科技</td>\n",
       "      <td>0.007445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>600276</td>\n",
       "      <td>恒瑞医药</td>\n",
       "      <td>0.007321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>688050</td>\n",
       "      <td>爱博医疗</td>\n",
       "      <td>0.007232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>688002</td>\n",
       "      <td>睿创微纳</td>\n",
       "      <td>0.007161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>600031</td>\n",
       "      <td>三一重工</td>\n",
       "      <td>0.007135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>688016</td>\n",
       "      <td>心脉医疗</td>\n",
       "      <td>0.006500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>600887</td>\n",
       "      <td>伊利股份</td>\n",
       "      <td>0.005479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>600754</td>\n",
       "      <td>锦江酒店</td>\n",
       "      <td>0.005437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>002142</td>\n",
       "      <td>宁波银行</td>\n",
       "      <td>0.005416</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>603501</td>\n",
       "      <td>韦尔股份</td>\n",
       "      <td>0.005195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>000739</td>\n",
       "      <td>普洛药业</td>\n",
       "      <td>0.004950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>603707</td>\n",
       "      <td>健友股份</td>\n",
       "      <td>0.004825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>300413</td>\n",
       "      <td>芒果超媒</td>\n",
       "      <td>0.004793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>300285</td>\n",
       "      <td>国瓷材料</td>\n",
       "      <td>0.004406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>601899</td>\n",
       "      <td>紫金矿业</td>\n",
       "      <td>0.004204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000651</td>\n",
       "      <td>格力电器</td>\n",
       "      <td>0.003916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>002304</td>\n",
       "      <td>洋河股份</td>\n",
       "      <td>0.003890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>HK1999</td>\n",
       "      <td>敏华控股</td>\n",
       "      <td>0.003538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>600845</td>\n",
       "      <td>宝信软件</td>\n",
       "      <td>0.003442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>600703</td>\n",
       "      <td>三安光电</td>\n",
       "      <td>0.002974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002</td>\n",
       "      <td>万科A</td>\n",
       "      <td>0.002636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>002044</td>\n",
       "      <td>美年健康</td>\n",
       "      <td>0.002434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>600048</td>\n",
       "      <td>保利地产</td>\n",
       "      <td>0.002335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>600309</td>\n",
       "      <td>万华化学</td>\n",
       "      <td>0.002143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>000786</td>\n",
       "      <td>北新建材</td>\n",
       "      <td>0.001853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>603833</td>\n",
       "      <td>欧派家居</td>\n",
       "      <td>0.001707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>300144</td>\n",
       "      <td>宋城演艺</td>\n",
       "      <td>0.001578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>600426</td>\n",
       "      <td>华鲁恒升</td>\n",
       "      <td>0.001318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>002460</td>\n",
       "      <td>赣锋锂业</td>\n",
       "      <td>0.000921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>300014</td>\n",
       "      <td>亿纬锂能</td>\n",
       "      <td>0.000852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>300454</td>\n",
       "      <td>深信服</td>\n",
       "      <td>0.000798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>000708</td>\n",
       "      <td>中信特钢</td>\n",
       "      <td>0.000722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>300750</td>\n",
       "      <td>宁德时代</td>\n",
       "      <td>0.000699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>002352</td>\n",
       "      <td>顺丰控股</td>\n",
       "      <td>0.000678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>002311</td>\n",
       "      <td>海大集团</td>\n",
       "      <td>0.000618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>601658</td>\n",
       "      <td>邮储银行</td>\n",
       "      <td>0.000567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>600406</td>\n",
       "      <td>国电南瑞</td>\n",
       "      <td>0.000555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>600660</td>\n",
       "      <td>福耀玻璃</td>\n",
       "      <td>0.000537</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      code   name  percentage\n",
       "18  HK0700   腾讯控股    0.029074\n",
       "40  600519   贵州茅台    0.026675\n",
       "60  688036   传音控股    0.024108\n",
       "8   000858    五粮液    0.023657\n",
       "66  688536    思瑞浦    0.020917\n",
       "24  300122   智飞生物    0.016150\n",
       "14  002415   海康威视    0.015070\n",
       "50  601318   中国平安    0.014735\n",
       "54  603259   药明康德    0.013237\n",
       "64  688185    康希诺    0.012456\n",
       "23  300015   爱尔眼科    0.012220\n",
       "20  HK3690   美团-W    0.012006\n",
       "0   000001   平安银行    0.011533\n",
       "2   000568   泸州老窖    0.011424\n",
       "42  600690   海尔智家    0.011378\n",
       "52  601888   中国中免    0.011088\n",
       "62  688111   金山办公    0.009738\n",
       "34  600036   招商银行    0.009490\n",
       "4   000661   长春高新    0.009398\n",
       "49  601166   兴业银行    0.009162\n",
       "16  002821    凯莱英    0.009083\n",
       "65  688396    华润微    0.008674\n",
       "27  300347   泰格医药    0.008553\n",
       "17  HK0388  香港交易所    0.008346\n",
       "45  600763   通策医疗    0.008080\n",
       "32  300760   迈瑞医疗    0.007937\n",
       "31  300759   康龙化成    0.007621\n",
       "46  600809   山西汾酒    0.007522\n",
       "21  HK6098  碧桂园服务    0.007522\n",
       "63  688169   石头科技    0.007445\n",
       "36  600276   恒瑞医药    0.007321\n",
       "61  688050   爱博医疗    0.007232\n",
       "58  688002   睿创微纳    0.007161\n",
       "33  600031   三一重工    0.007135\n",
       "59  688016   心脉医疗    0.006500\n",
       "48  600887   伊利股份    0.005479\n",
       "44  600754   锦江酒店    0.005437\n",
       "10  002142   宁波银行    0.005416\n",
       "55  603501   韦尔股份    0.005195\n",
       "6   000739   普洛药业    0.004950\n",
       "56  603707   健友股份    0.004825\n",
       "28  300413   芒果超媒    0.004793\n",
       "26  300285   国瓷材料    0.004406\n",
       "53  601899   紫金矿业    0.004204\n",
       "3   000651   格力电器    0.003916\n",
       "11  002304   洋河股份    0.003890\n",
       "19  HK1999   敏华控股    0.003538\n",
       "47  600845   宝信软件    0.003442\n",
       "43  600703   三安光电    0.002974\n",
       "1   000002    万科A    0.002636\n",
       "9   002044   美年健康    0.002434\n",
       "35  600048   保利地产    0.002335\n",
       "37  600309   万华化学    0.002143\n",
       "7   000786   北新建材    0.001853\n",
       "57  603833   欧派家居    0.001707\n",
       "25  300144   宋城演艺    0.001578\n",
       "39  600426   华鲁恒升    0.001318\n",
       "15  002460   赣锋锂业    0.000921\n",
       "22  300014   亿纬锂能    0.000852\n",
       "29  300454    深信服    0.000798\n",
       "5   000708   中信特钢    0.000722\n",
       "30  300750   宁德时代    0.000699\n",
       "13  002352   顺丰控股    0.000678\n",
       "12  002311   海大集团    0.000618\n",
       "51  601658   邮储银行    0.000567\n",
       "38  600406   国电南瑞    0.000555\n",
       "41  600660   福耀玻璃    0.000537"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['code'] = data['code'].map(lambda x: 'HK'+x[1:] if len(x)<6 else x)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>门类名称及代码</th>\n",
       "      <th>行业大类名称</th>\n",
       "      <th>上市公司代码</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>农业</td>\n",
       "      <td>000998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>农业</td>\n",
       "      <td>002041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>农业</td>\n",
       "      <td>002772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>农业</td>\n",
       "      <td>300087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>农业</td>\n",
       "      <td>300189</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       门类名称及代码 行业大类名称  上市公司代码\n",
       "0  农、林、牧、渔业(A)     农业  000998\n",
       "1  农、林、牧、渔业(A)     农业  002041\n",
       "2  农、林、牧、渔业(A)     农业  002772\n",
       "3  农、林、牧、渔业(A)     农业  300087\n",
       "4  农、林、牧、渔业(A)     农业  300189"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = create_engine(\"mysql+pymysql://root:123456@172.17.0.3:3306/stock?charset=utf8\")\n",
    "sql = \"select 门类名称及代码,行业大类名称,上市公司代码 from industry_info\"\n",
    "industry_info = pd.read_sql_query(sql, engine)\n",
    "industry_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>percentage</th>\n",
       "      <th>门类名称及代码</th>\n",
       "      <th>行业大类名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HK0700</td>\n",
       "      <td>腾讯控股</td>\n",
       "      <td>0.029074</td>\n",
       "      <td>港股</td>\n",
       "      <td>港股</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>600519</td>\n",
       "      <td>贵州茅台</td>\n",
       "      <td>0.026675</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>酒、饮料和精制茶制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>688036</td>\n",
       "      <td>传音控股</td>\n",
       "      <td>0.024108</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>计算机、通信和其他电子设备制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000858</td>\n",
       "      <td>五粮液</td>\n",
       "      <td>0.023657</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>酒、饮料和精制茶制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>688536</td>\n",
       "      <td>思瑞浦</td>\n",
       "      <td>0.020917</td>\n",
       "      <td>信息传输、软件和信息技术服务业(I)</td>\n",
       "      <td>软件和信息技术服务业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>300122</td>\n",
       "      <td>智飞生物</td>\n",
       "      <td>0.016150</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>医药制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>002415</td>\n",
       "      <td>海康威视</td>\n",
       "      <td>0.015070</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>计算机、通信和其他电子设备制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>601318</td>\n",
       "      <td>中国平安</td>\n",
       "      <td>0.014735</td>\n",
       "      <td>金融业(J)</td>\n",
       "      <td>保险业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>603259</td>\n",
       "      <td>药明康德</td>\n",
       "      <td>0.013237</td>\n",
       "      <td>科学研究和技术服务业(M)</td>\n",
       "      <td>研究和试验发展</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>688185</td>\n",
       "      <td>康希诺</td>\n",
       "      <td>0.012456</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>医药制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>300015</td>\n",
       "      <td>爱尔眼科</td>\n",
       "      <td>0.012220</td>\n",
       "      <td>卫生和社会工作业(Q)</td>\n",
       "      <td>卫生</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>HK3690</td>\n",
       "      <td>美团-W</td>\n",
       "      <td>0.012006</td>\n",
       "      <td>港股</td>\n",
       "      <td>港股</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>000001</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>0.011533</td>\n",
       "      <td>金融业(J)</td>\n",
       "      <td>货币金融服务</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>000568</td>\n",
       "      <td>泸州老窖</td>\n",
       "      <td>0.011424</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>酒、饮料和精制茶制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>600690</td>\n",
       "      <td>海尔智家</td>\n",
       "      <td>0.011378</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>电气机械及器材制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>601888</td>\n",
       "      <td>中国中免</td>\n",
       "      <td>0.011088</td>\n",
       "      <td>租赁和商务服务业(L)</td>\n",
       "      <td>商务服务业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>688111</td>\n",
       "      <td>金山办公</td>\n",
       "      <td>0.009738</td>\n",
       "      <td>信息传输、软件和信息技术服务业(I)</td>\n",
       "      <td>软件和信息技术服务业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>600036</td>\n",
       "      <td>招商银行</td>\n",
       "      <td>0.009490</td>\n",
       "      <td>金融业(J)</td>\n",
       "      <td>货币金融服务</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>000661</td>\n",
       "      <td>长春高新</td>\n",
       "      <td>0.009398</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>医药制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>601166</td>\n",
       "      <td>兴业银行</td>\n",
       "      <td>0.009162</td>\n",
       "      <td>金融业(J)</td>\n",
       "      <td>货币金融服务</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>002821</td>\n",
       "      <td>凯莱英</td>\n",
       "      <td>0.009083</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>医药制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>688396</td>\n",
       "      <td>华润微</td>\n",
       "      <td>0.008674</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>计算机、通信和其他电子设备制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>300347</td>\n",
       "      <td>泰格医药</td>\n",
       "      <td>0.008553</td>\n",
       "      <td>卫生和社会工作业(Q)</td>\n",
       "      <td>卫生</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>HK0388</td>\n",
       "      <td>香港交易所</td>\n",
       "      <td>0.008346</td>\n",
       "      <td>港股</td>\n",
       "      <td>港股</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>600763</td>\n",
       "      <td>通策医疗</td>\n",
       "      <td>0.008080</td>\n",
       "      <td>卫生和社会工作业(Q)</td>\n",
       "      <td>卫生</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>300760</td>\n",
       "      <td>迈瑞医疗</td>\n",
       "      <td>0.007937</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>专用设备制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>300759</td>\n",
       "      <td>康龙化成</td>\n",
       "      <td>0.007621</td>\n",
       "      <td>科学研究和技术服务业(M)</td>\n",
       "      <td>研究和试验发展</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>600809</td>\n",
       "      <td>山西汾酒</td>\n",
       "      <td>0.007522</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>酒、饮料和精制茶制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>HK6098</td>\n",
       "      <td>碧桂园服务</td>\n",
       "      <td>0.007522</td>\n",
       "      <td>港股</td>\n",
       "      <td>港股</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>688169</td>\n",
       "      <td>石头科技</td>\n",
       "      <td>0.007445</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>电气机械及器材制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>600276</td>\n",
       "      <td>恒瑞医药</td>\n",
       "      <td>0.007321</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>医药制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>688050</td>\n",
       "      <td>爱博医疗</td>\n",
       "      <td>0.007232</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>专用设备制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>688002</td>\n",
       "      <td>睿创微纳</td>\n",
       "      <td>0.007161</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>计算机、通信和其他电子设备制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>600031</td>\n",
       "      <td>三一重工</td>\n",
       "      <td>0.007135</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>专用设备制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>688016</td>\n",
       "      <td>心脉医疗</td>\n",
       "      <td>0.006500</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>专用设备制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>600887</td>\n",
       "      <td>伊利股份</td>\n",
       "      <td>0.005479</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>食品制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>600754</td>\n",
       "      <td>锦江酒店</td>\n",
       "      <td>0.005437</td>\n",
       "      <td>住宿和餐饮业(H)</td>\n",
       "      <td>住宿业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>002142</td>\n",
       "      <td>宁波银行</td>\n",
       "      <td>0.005416</td>\n",
       "      <td>金融业(J)</td>\n",
       "      <td>货币金融服务</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>603501</td>\n",
       "      <td>韦尔股份</td>\n",
       "      <td>0.005195</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>计算机、通信和其他电子设备制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>000739</td>\n",
       "      <td>普洛药业</td>\n",
       "      <td>0.004950</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>医药制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>603707</td>\n",
       "      <td>健友股份</td>\n",
       "      <td>0.004825</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>医药制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>300413</td>\n",
       "      <td>芒果超媒</td>\n",
       "      <td>0.004793</td>\n",
       "      <td>文化、体育和娱乐业(R)</td>\n",
       "      <td>文化艺术业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>300285</td>\n",
       "      <td>国瓷材料</td>\n",
       "      <td>0.004406</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>化学原料及化学制品制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>601899</td>\n",
       "      <td>紫金矿业</td>\n",
       "      <td>0.004204</td>\n",
       "      <td>采矿业(B)</td>\n",
       "      <td>有色金属矿采选业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>000651</td>\n",
       "      <td>格力电器</td>\n",
       "      <td>0.003916</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>电气机械及器材制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>002304</td>\n",
       "      <td>洋河股份</td>\n",
       "      <td>0.003890</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>酒、饮料和精制茶制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>HK1999</td>\n",
       "      <td>敏华控股</td>\n",
       "      <td>0.003538</td>\n",
       "      <td>港股</td>\n",
       "      <td>港股</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>600845</td>\n",
       "      <td>宝信软件</td>\n",
       "      <td>0.003442</td>\n",
       "      <td>信息传输、软件和信息技术服务业(I)</td>\n",
       "      <td>软件和信息技术服务业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>600703</td>\n",
       "      <td>三安光电</td>\n",
       "      <td>0.002974</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>计算机、通信和其他电子设备制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>000002</td>\n",
       "      <td>万科A</td>\n",
       "      <td>0.002636</td>\n",
       "      <td>房地产业(K)</td>\n",
       "      <td>房地产业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>002044</td>\n",
       "      <td>美年健康</td>\n",
       "      <td>0.002434</td>\n",
       "      <td>卫生和社会工作业(Q)</td>\n",
       "      <td>卫生</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>600048</td>\n",
       "      <td>保利地产</td>\n",
       "      <td>0.002335</td>\n",
       "      <td>房地产业(K)</td>\n",
       "      <td>房地产业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>600309</td>\n",
       "      <td>万华化学</td>\n",
       "      <td>0.002143</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>化学原料及化学制品制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>000786</td>\n",
       "      <td>北新建材</td>\n",
       "      <td>0.001853</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>非金属矿物制品业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>603833</td>\n",
       "      <td>欧派家居</td>\n",
       "      <td>0.001707</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>家具制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>300144</td>\n",
       "      <td>宋城演艺</td>\n",
       "      <td>0.001578</td>\n",
       "      <td>文化、体育和娱乐业(R)</td>\n",
       "      <td>文化艺术业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>600426</td>\n",
       "      <td>华鲁恒升</td>\n",
       "      <td>0.001318</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>化学原料及化学制品制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>002460</td>\n",
       "      <td>赣锋锂业</td>\n",
       "      <td>0.000921</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>有色金属冶炼及压延加工业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>300014</td>\n",
       "      <td>亿纬锂能</td>\n",
       "      <td>0.000852</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>电气机械及器材制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>300454</td>\n",
       "      <td>深信服</td>\n",
       "      <td>0.000798</td>\n",
       "      <td>信息传输、软件和信息技术服务业(I)</td>\n",
       "      <td>软件和信息技术服务业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>000708</td>\n",
       "      <td>中信特钢</td>\n",
       "      <td>0.000722</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>黑色金属冶炼及压延加工业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>300750</td>\n",
       "      <td>宁德时代</td>\n",
       "      <td>0.000699</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>电气机械及器材制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>002352</td>\n",
       "      <td>顺丰控股</td>\n",
       "      <td>0.000678</td>\n",
       "      <td>交通运输、仓储和邮政业(G)</td>\n",
       "      <td>邮政业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>002311</td>\n",
       "      <td>海大集团</td>\n",
       "      <td>0.000618</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>农副食品加工业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>601658</td>\n",
       "      <td>邮储银行</td>\n",
       "      <td>0.000567</td>\n",
       "      <td>金融业(J)</td>\n",
       "      <td>货币金融服务</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>600406</td>\n",
       "      <td>国电南瑞</td>\n",
       "      <td>0.000555</td>\n",
       "      <td>信息传输、软件和信息技术服务业(I)</td>\n",
       "      <td>软件和信息技术服务业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>600660</td>\n",
       "      <td>福耀玻璃</td>\n",
       "      <td>0.000537</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>非金属矿物制品业</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      code   name  percentage             门类名称及代码            行业大类名称\n",
       "0   HK0700   腾讯控股    0.029074                  港股                港股\n",
       "1   600519   贵州茅台    0.026675              制造业(C)       酒、饮料和精制茶制造业\n",
       "2   688036   传音控股    0.024108              制造业(C)  计算机、通信和其他电子设备制造业\n",
       "3   000858    五粮液    0.023657              制造业(C)       酒、饮料和精制茶制造业\n",
       "4   688536    思瑞浦    0.020917  信息传输、软件和信息技术服务业(I)        软件和信息技术服务业\n",
       "5   300122   智飞生物    0.016150              制造业(C)             医药制造业\n",
       "6   002415   海康威视    0.015070              制造业(C)  计算机、通信和其他电子设备制造业\n",
       "7   601318   中国平安    0.014735              金融业(J)               保险业\n",
       "8   603259   药明康德    0.013237       科学研究和技术服务业(M)           研究和试验发展\n",
       "9   688185    康希诺    0.012456              制造业(C)             医药制造业\n",
       "10  300015   爱尔眼科    0.012220         卫生和社会工作业(Q)                卫生\n",
       "11  HK3690   美团-W    0.012006                  港股                港股\n",
       "12  000001   平安银行    0.011533              金融业(J)            货币金融服务\n",
       "13  000568   泸州老窖    0.011424              制造业(C)       酒、饮料和精制茶制造业\n",
       "14  600690   海尔智家    0.011378              制造业(C)        电气机械及器材制造业\n",
       "15  601888   中国中免    0.011088         租赁和商务服务业(L)             商务服务业\n",
       "16  688111   金山办公    0.009738  信息传输、软件和信息技术服务业(I)        软件和信息技术服务业\n",
       "17  600036   招商银行    0.009490              金融业(J)            货币金融服务\n",
       "18  000661   长春高新    0.009398              制造业(C)             医药制造业\n",
       "19  601166   兴业银行    0.009162              金融业(J)            货币金融服务\n",
       "20  002821    凯莱英    0.009083              制造业(C)             医药制造业\n",
       "21  688396    华润微    0.008674              制造业(C)  计算机、通信和其他电子设备制造业\n",
       "22  300347   泰格医药    0.008553         卫生和社会工作业(Q)                卫生\n",
       "23  HK0388  香港交易所    0.008346                  港股                港股\n",
       "24  600763   通策医疗    0.008080         卫生和社会工作业(Q)                卫生\n",
       "25  300760   迈瑞医疗    0.007937              制造业(C)           专用设备制造业\n",
       "26  300759   康龙化成    0.007621       科学研究和技术服务业(M)           研究和试验发展\n",
       "27  600809   山西汾酒    0.007522              制造业(C)       酒、饮料和精制茶制造业\n",
       "28  HK6098  碧桂园服务    0.007522                  港股                港股\n",
       "29  688169   石头科技    0.007445              制造业(C)        电气机械及器材制造业\n",
       "30  600276   恒瑞医药    0.007321              制造业(C)             医药制造业\n",
       "31  688050   爱博医疗    0.007232              制造业(C)           专用设备制造业\n",
       "32  688002   睿创微纳    0.007161              制造业(C)  计算机、通信和其他电子设备制造业\n",
       "33  600031   三一重工    0.007135              制造业(C)           专用设备制造业\n",
       "34  688016   心脉医疗    0.006500              制造业(C)           专用设备制造业\n",
       "35  600887   伊利股份    0.005479              制造业(C)             食品制造业\n",
       "36  600754   锦江酒店    0.005437           住宿和餐饮业(H)               住宿业\n",
       "37  002142   宁波银行    0.005416              金融业(J)            货币金融服务\n",
       "38  603501   韦尔股份    0.005195              制造业(C)  计算机、通信和其他电子设备制造业\n",
       "39  000739   普洛药业    0.004950              制造业(C)             医药制造业\n",
       "40  603707   健友股份    0.004825              制造业(C)             医药制造业\n",
       "41  300413   芒果超媒    0.004793        文化、体育和娱乐业(R)             文化艺术业\n",
       "42  300285   国瓷材料    0.004406              制造业(C)      化学原料及化学制品制造业\n",
       "43  601899   紫金矿业    0.004204              采矿业(B)          有色金属矿采选业\n",
       "44  000651   格力电器    0.003916              制造业(C)        电气机械及器材制造业\n",
       "45  002304   洋河股份    0.003890              制造业(C)       酒、饮料和精制茶制造业\n",
       "46  HK1999   敏华控股    0.003538                  港股                港股\n",
       "47  600845   宝信软件    0.003442  信息传输、软件和信息技术服务业(I)        软件和信息技术服务业\n",
       "48  600703   三安光电    0.002974              制造业(C)  计算机、通信和其他电子设备制造业\n",
       "49  000002    万科A    0.002636             房地产业(K)              房地产业\n",
       "50  002044   美年健康    0.002434         卫生和社会工作业(Q)                卫生\n",
       "51  600048   保利地产    0.002335             房地产业(K)              房地产业\n",
       "52  600309   万华化学    0.002143              制造业(C)      化学原料及化学制品制造业\n",
       "53  000786   北新建材    0.001853              制造业(C)          非金属矿物制品业\n",
       "54  603833   欧派家居    0.001707              制造业(C)             家具制造业\n",
       "55  300144   宋城演艺    0.001578        文化、体育和娱乐业(R)             文化艺术业\n",
       "56  600426   华鲁恒升    0.001318              制造业(C)      化学原料及化学制品制造业\n",
       "57  002460   赣锋锂业    0.000921              制造业(C)      有色金属冶炼及压延加工业\n",
       "58  300014   亿纬锂能    0.000852              制造业(C)        电气机械及器材制造业\n",
       "59  300454    深信服    0.000798  信息传输、软件和信息技术服务业(I)        软件和信息技术服务业\n",
       "60  000708   中信特钢    0.000722              制造业(C)      黑色金属冶炼及压延加工业\n",
       "61  300750   宁德时代    0.000699              制造业(C)        电气机械及器材制造业\n",
       "62  002352   顺丰控股    0.000678      交通运输、仓储和邮政业(G)               邮政业\n",
       "63  002311   海大集团    0.000618              制造业(C)           农副食品加工业\n",
       "64  601658   邮储银行    0.000567              金融业(J)            货币金融服务\n",
       "65  600406   国电南瑞    0.000555  信息传输、软件和信息技术服务业(I)        软件和信息技术服务业\n",
       "66  600660   福耀玻璃    0.000537              制造业(C)          非金属矿物制品业"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info = pd.merge(data,industry_info,left_on=\"code\",right_on='上市公司代码',how='left')\n",
    "data_info = data_info.fillna('港股')\n",
    "del data_info['上市公司代码']\n",
    "data_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>门类名称及代码</th>\n",
       "      <th>percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>0.273330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>港股</td>\n",
       "      <td>0.060486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>金融业(J)</td>\n",
       "      <td>0.050904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>信息传输、软件和信息技术服务业(I)</td>\n",
       "      <td>0.035450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>卫生和社会工作业(Q)</td>\n",
       "      <td>0.031286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>科学研究和技术服务业(M)</td>\n",
       "      <td>0.020858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>租赁和商务服务业(L)</td>\n",
       "      <td>0.011088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>文化、体育和娱乐业(R)</td>\n",
       "      <td>0.006371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>住宿和餐饮业(H)</td>\n",
       "      <td>0.005437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>房地产业(K)</td>\n",
       "      <td>0.004972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>采矿业(B)</td>\n",
       "      <td>0.004204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>交通运输、仓储和邮政业(G)</td>\n",
       "      <td>0.000678</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               门类名称及代码  percentage\n",
       "3               制造业(C)    0.273330\n",
       "7                   港股    0.060486\n",
       "11              金融业(J)    0.050904\n",
       "2   信息传输、软件和信息技术服务业(I)    0.035450\n",
       "4          卫生和社会工作业(Q)    0.031286\n",
       "8        科学研究和技术服务业(M)    0.020858\n",
       "9          租赁和商务服务业(L)    0.011088\n",
       "6         文化、体育和娱乐业(R)    0.006371\n",
       "1            住宿和餐饮业(H)    0.005437\n",
       "5              房地产业(K)    0.004972\n",
       "10              采矿业(B)    0.004204\n",
       "0       交通运输、仓储和邮政业(G)    0.000678"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info.groupby('门类名称及代码',as_index=False).sum().sort_values('percentage',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>行业大类名称</th>\n",
       "      <th>percentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>酒、饮料和精制茶制造业</td>\n",
       "      <td>0.073168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>医药制造业</td>\n",
       "      <td>0.064182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>计算机、通信和其他电子设备制造业</td>\n",
       "      <td>0.063182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>港股</td>\n",
       "      <td>0.060486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>货币金融服务</td>\n",
       "      <td>0.036169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>软件和信息技术服务业</td>\n",
       "      <td>0.035450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>卫生</td>\n",
       "      <td>0.031286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>专用设备制造业</td>\n",
       "      <td>0.028804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>电气机械及器材制造业</td>\n",
       "      <td>0.024289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>研究和试验发展</td>\n",
       "      <td>0.020858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>保险业</td>\n",
       "      <td>0.014735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>商务服务业</td>\n",
       "      <td>0.011088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>化学原料及化学制品制造业</td>\n",
       "      <td>0.007866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>文化艺术业</td>\n",
       "      <td>0.006371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>食品制造业</td>\n",
       "      <td>0.005479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>住宿业</td>\n",
       "      <td>0.005437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>房地产业</td>\n",
       "      <td>0.004972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>有色金属矿采选业</td>\n",
       "      <td>0.004204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>非金属矿物制品业</td>\n",
       "      <td>0.002390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>家具制造业</td>\n",
       "      <td>0.001707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>有色金属冶炼及压延加工业</td>\n",
       "      <td>0.000921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>黑色金属冶炼及压延加工业</td>\n",
       "      <td>0.000722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>邮政业</td>\n",
       "      <td>0.000678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>农副食品加工业</td>\n",
       "      <td>0.000618</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              行业大类名称  percentage\n",
       "20       酒、饮料和精制茶制造业    0.073168\n",
       "5              医药制造业    0.064182\n",
       "16  计算机、通信和其他电子设备制造业    0.063182\n",
       "13                港股    0.060486\n",
       "17            货币金融服务    0.036169\n",
       "18        软件和信息技术服务业    0.035450\n",
       "6                 卫生    0.031286\n",
       "0            专用设备制造业    0.028804\n",
       "14        电气机械及器材制造业    0.024289\n",
       "15           研究和试验发展    0.020858\n",
       "2                保险业    0.014735\n",
       "7              商务服务业    0.011088\n",
       "4       化学原料及化学制品制造业    0.007866\n",
       "10             文化艺术业    0.006371\n",
       "22             食品制造业    0.005479\n",
       "1                住宿业    0.005437\n",
       "9               房地产业    0.004972\n",
       "12          有色金属矿采选业    0.004204\n",
       "21          非金属矿物制品业    0.002390\n",
       "8              家具制造业    0.001707\n",
       "11      有色金属冶炼及压延加工业    0.000921\n",
       "23      黑色金属冶炼及压延加工业    0.000722\n",
       "19               邮政业    0.000678\n",
       "3            农副食品加工业    0.000618"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info.groupby('行业大类名称',as_index=False).sum().sort_values('percentage',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>股票代号</th>\n",
       "      <th>公司简称</th>\n",
       "      <th>持仓百分比</th>\n",
       "      <th>门类名称及代码</th>\n",
       "      <th>行业大类名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HK0700</td>\n",
       "      <td>腾讯控股</td>\n",
       "      <td>0.029074</td>\n",
       "      <td>港股</td>\n",
       "      <td>港股</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>600519</td>\n",
       "      <td>贵州茅台</td>\n",
       "      <td>0.026675</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>酒、饮料和精制茶制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>688036</td>\n",
       "      <td>传音控股</td>\n",
       "      <td>0.024108</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>计算机、通信和其他电子设备制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000858</td>\n",
       "      <td>五粮液</td>\n",
       "      <td>0.023657</td>\n",
       "      <td>制造业(C)</td>\n",
       "      <td>酒、饮料和精制茶制造业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>688536</td>\n",
       "      <td>思瑞浦</td>\n",
       "      <td>0.020917</td>\n",
       "      <td>信息传输、软件和信息技术服务业(I)</td>\n",
       "      <td>软件和信息技术服务业</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     股票代号  公司简称     持仓百分比             门类名称及代码            行业大类名称\n",
       "0  HK0700  腾讯控股  0.029074                  港股                港股\n",
       "1  600519  贵州茅台  0.026675              制造业(C)       酒、饮料和精制茶制造业\n",
       "2  688036  传音控股  0.024108              制造业(C)  计算机、通信和其他电子设备制造业\n",
       "3  000858   五粮液  0.023657              制造业(C)       酒、饮料和精制茶制造业\n",
       "4  688536   思瑞浦  0.020917  信息传输、软件和信息技术服务业(I)        软件和信息技术服务业"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info = data_info.rename(columns={'code':'股票代号','name':'公司简称','percentage':'持仓百分比'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def write_to_excel(name,cor_df): # cor_df 为要保存的 dataframe \n",
    "    writer = pd.ExcelWriter(name +'.xlsx', engine='xlsxwriter') # 这里用\n",
    "    cor_df.to_excel(writer,sheet_name='Sheet1', encoding='utf8', header=False, index=False, startcol=0, startrow=1) # 把dataframe的数据从第2行开始\n",
    "    workbook  = writer.book\n",
    "    \n",
    "    format1 = workbook.add_format({ # 先把样式打包，然后之后赋值即可\n",
    "        'bold': True, # 字体加粗\n",
    "        'text_wrap': True, # 是否自动换行\n",
    "        'valign': 'bottom',  #垂直对齐方式\n",
    "        'align': 'center', # 水平对齐方式\n",
    "        'fg_color': '#D7E4BC', # 单元格背景颜色\n",
    "        'border': 1,# 边框\n",
    "    })    \n",
    "    writer_sheet = writer.sheets['Sheet1']\n",
    "    # 设置宽度\n",
    "    writer_sheet.set_column(\"A:E\", 20)\n",
    "    # merge_range(first_row, first_col, last_row, last_col, data[, cell_format])\n",
    "#     writer_sheet.merge_range(0,0,1,0,'1',format1)\n",
    "#     writer_sheet.merge_range(0,1,1,1,'2',format1)\n",
    "#     writer_sheet.merge_range(0,2,1,2,'3',format1)\n",
    "#     writer_sheet.merge_range(0,3,1,3,'4',format1)\n",
    "#     writer_sheet.merge_range(0,8,1,8,'5',format1)\n",
    "#     writer_sheet.merge_range(0,4,0,7,'6',format1)\n",
    "#     writer_sheet.write(1,4,'a',format1)\n",
    "#     writer_sheet.write(1,5,'b',format1)\n",
    "#     writer_sheet.write(1,6,'c',format1)\n",
    "#     writer_sheet.write(1,7,'d',format1)\n",
    "    writer.save()\n",
    "    writer.close()"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "write_to_excel('industry',data_info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_info.to_excel('行业持仓占比.xlsx',encoding=\"utf_8_sig\",index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
